using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;
using System.Reflection;
using System.Xml;
using System.Text;
using System.Text.RegularExpressions;
using ADODB;
using AMFC.Exceptions;

namespace AMFC{
	public enum CallSPMode{ Nothing, Value, Record,DataSet};
	public enum ExecuteMode{ Nothing, Value, DataSet, DataReader};
	public enum SQLXMLRequestMode{ XML, Text };
	public enum SPParametesList{ CommandTimeout };
	/// <summary>
	/// AFC SQL Server database layer
	/// </summary>
	public class SQLServer{
		protected SqlConnection m_conn = null;
		protected string m_connString = null;
		
		public SQLServer(){}
		public SQLServer(string connection){m_connString = connection;}
		~SQLServer(){Close();}

		public SqlConnection Connection{
			get{
				if( m_conn == null )m_conn = DBConnection();
				if( m_conn.State == ConnectionState.Closed )m_conn.Open();
				return m_conn;
			}
		}
		public Connection ConnectionADO{
			get{
				ADODB.Connection conn = new ADODB.Connection();
				conn.Open(Registry.dbConnectionADO,"","",-1);
				return conn;
			}
		}
		public void Close(){if( m_conn != null && m_conn.State == ConnectionState.Open )m_conn.Close();}
		
		public void UpdateField(int applID, string viewName, string fieldName, object fieldValue){
			UpdateField(viewName,applID,fieldName,fieldValue);
		}
		public void UpdateField(int applID, string fieldName, object fieldValue){
			UpdateField("t_Applicants",applID,fieldName,fieldValue);
		}
		public void UpdateField(string viewName,int applID, string fieldName, object fieldValue){
			if( fieldValue.GetType().Name == "XmlNode[]" )
				fieldValue = ((XmlNode[])fieldValue)[0].Value;
			string sql = "SELECT ApplicantID,["+fieldName+"] FROM "+DBObjectNameSafe(viewName)+" WHERE ApplicantID = "+applID;
			string baseTable = "";
			SqlCommand comm = new SqlCommand(sql,Connection);
			SqlDataReader dr = comm.ExecuteReader(CommandBehavior.KeyInfo);
			DataView dvSchema = dr.GetSchemaTable().DefaultView;
			dvSchema.RowFilter = "ColumnName = '"+fieldName+"'";
			try{baseTable =  (String)dvSchema[0]["BaseTableName"];
			}catch(Exception){ throw new Exception("\n\nColumn ["+fieldName+"] does not belond to any table.\n\n"); }
			dr.Close();
			if( (bool)dvSchema[0]["AllowDBNull"] == true && fieldValue.GetType().Name == "Object" )
					fieldValue = DBNull.Value;
			if( (String)dvSchema[0]["BaseColumnName"] != null )
				fieldName = (String)dvSchema[0]["BaseColumnName"];
			sql = "UPDATE " + DBObjectNameSafe(baseTable) + " SET " + DBObjectNameSafe(fieldName) + " = @Field WHERE ApplicantID=@ApplID";
			ExecText(sql,fieldValue,applID);
		}
		//
		public SqlConnection DBConnection(){
			return DBConnection(m_connString);
		}
		public SqlConnection DBConnection(string connStr){
			if( connStr == null ){
				UserObject uo = new UserObject();
				connStr = Registry.dbConnection+";Workstation ID="+uo.group+"."+uo.name;
			}
			SqlConnection conn = new SqlConnection(connStr);
			conn.Open();
			return conn;
		}

		#region ================= ExecSP ===================
		public object ExecSP(string procName,CallSPMode mode,params object[] arrayOfParams) {
			return ExecSP(procName,mode,new SortedList(),arrayOfParams);
		}
		public object ExecSP(string procName,CallSPMode mode,SortedList parameters,params object[] arrayOfParams) {
			return ExecSP(Connection,procName,mode,parameters,arrayOfParams);
		}
		public object ExecSP(SqlConnection conn,string procName,CallSPMode mode,params object[] arrayOfParams) {
			return ExecSP(conn,procName,mode,new SortedList(),arrayOfParams);
		}
		public object ExecSP(SqlConnection conn,string procName,CallSPMode mode,SortedList parameters,params object[] arrayOfParams){
			SqlCommand comm = new SqlCommand(procName,conn);
			comm.CommandType = CommandType.StoredProcedure;
			if( parameters.ContainsKey(SPParametesList.CommandTimeout) )comm.CommandTimeout = (int)parameters[SPParametesList.CommandTimeout];
			SqlCommandBuilder.DeriveParameters(comm);
			for( int i=1; i < comm.Parameters.Count && i < arrayOfParams.GetLength(0)+1; i++ ){
				comm.Parameters[i].Value = arrayOfParams[i-1];
			}
			if( mode == CallSPMode.DataSet ){
				SqlDataAdapter da = new SqlDataAdapter(comm);
				DataSet ds = new DataSet();
				da.Fill(ds);
				return ds;
			}
			SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
			comm.Connection.Close();
			if( mode == CallSPMode.Nothing )return null;
			if( mode == CallSPMode.Record )return dr;
			if( mode == CallSPMode.Value )return comm.Parameters[0].Value;
			return null;
		}
		#endregion
		
		public SqlCommand PrepCommand(SqlConnection conn,string sql,params object[] arrayOfParams){
			SqlCommand comm = new SqlCommand(sql,conn);
			MatchCollection mParams = Regex.Matches(sql,@"(@\w+)",RegexOptions.ECMAScript);
			for( int g=0; g < mParams.Count && g < arrayOfParams.Length; g++ )
				comm.Parameters.Add(mParams[g].Value,arrayOfParams[g]==null?DBNull.Value:arrayOfParams[g]);
			return comm;
		}

		private string DBObjectNameSafe(string dbObjectName){
			dbObjectName = dbObjectName.Trim();
			return dbObjectName.IndexOf("[")==0 ? dbObjectName : "["+dbObjectName+"]";
		}

		public byte[] GetImageFolder(int applID,int folderID){
			SqlDataReader dr = null;
			bool hasRecords = Execute("SELECT Data FROM t_Image WHERE ApplicantID="+applID+" AND ImageTypeID="+folderID,ref dr);
			if( !hasRecords )throw new NoImageFolderException(folderID);
			return dr.GetSqlBinary(0).Value;
		}
		public object GetFieldByApplID(string tableName,string fieldName,int applID) {
			SqlDataReader dr = null;
			Execute("SELECT "+DBObjectNameSafe(fieldName)+" FROM "+tableName+" WHERE ApplicantID="+applID,ref dr);
			return dr[fieldName];
		}
		#region ================= Execute =====================
		public DataSet ExecText( string sql,params object[] aParams){
			SqlCommand c = PrepCommand(Connection,sql,aParams);
			SqlDataAdapter da = new SqlDataAdapter(c);
			DataSet ds = new DataSet();
			da.Fill(ds);
			Close();
			return ds;
		}
		public object ExecuteScalar(string sql){
			SqlCommand c = new SqlCommand(sql,Connection);
			object o = c.ExecuteScalar();
			c.Connection.Close();
			return o;
		}
		public SqlDataReader Execute(string sql) {
			SqlCommand comm = new SqlCommand(sql,Connection);
			return comm.ExecuteReader(CommandBehavior.CloseConnection);
		}
		public bool Execute(string sql,ref SqlDataReader dataReader){
			return Execute(sql, ref dataReader, true );
		}
		public bool Execute(string sql,ref SqlDataReader dataReader, bool read) {
			SqlCommand comm = new SqlCommand(sql,Connection);
			OpenConnection();
			dataReader = comm.ExecuteReader(CommandBehavior.CloseConnection);
			return read ? dataReader.Read() : false;
		}
		public void Execute(string sql,ref DataTable dataTable) {
			SqlDataAdapter da = new SqlDataAdapter(sql,Connection);
			da.Fill(dataTable);
			Connection.Close();
		}
		public DataSet Execute(string sql,string tableName) {
			SqlDataAdapter da = new SqlDataAdapter(sql,Connection);
			DataSet ds = new DataSet();
			da.Fill(ds);
			Connection.Close();
			return ds;
		}
		public ADODB.Recordset Execute(string sql,ADODB.CursorTypeEnum cursor ){
			ADODB.Connection conn = ConnectionADO;
			ADODB.Recordset rs = new ADODB.Recordset();
			rs.Open(sql,conn,cursor,ADODB.LockTypeEnum.adLockBatchOptimistic,(int)ADODB.CommandTypeEnum.adCmdText);
			if( cursor == ADODB.CursorTypeEnum.adOpenForwardOnly )
				conn.Close();
			return rs;
		}
		#endregion

		#region =============== ADO =================
		public ADODB.Recordset ExeciteADO(string sql){
			ADODB.Recordset rs = new ADODB.RecordsetClass();
			rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
			rs.Open(sql,ConnectionADO,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockBatchOptimistic,0);
			return rs;
		}
		public string PersistRSToString(ADODB.Recordset rs){
			ADODB.Stream strm = new ADODB.StreamClass();
			strm.Type = ADODB.StreamTypeEnum.adTypeText;
			strm.Open(Missing.Value,ADODB.ConnectModeEnum.adModeUnknown,ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified,"","");
			rs.Save(strm,ADODB.PersistFormatEnum.adPersistXML);
			return strm.ReadText(-1);
		}
		#endregion
		
		public string ExecuteXMLRequest(string sql){
			SqlDataReader dr = null;
			if( !Execute(sql,ref dr) )return "";
			StringBuilder sb = new StringBuilder();
			sb.Append(dr[0].ToString());
			while( dr.Read() )
				sb.Append(dr[0].ToString());
			return sb.ToString();
		}
		public object GetField(string sql){
			SqlDataReader dr = null;
			Execute(sql,ref dr);
			return dr.GetValue(0);
		}
		private void OpenConnection(){
			if( Connection.State == ConnectionState.Closed )Connection.Open();
		}
		#region Junk +++++++++++++++++
		private object ExecuteXMLRequest_(string sql,SQLXMLRequestMode mode){
//					CONST adWriteChar = 0
//						CONST adExecuteStream = 1024
			object e = Missing.Value;
			ADODB.Connection conn = ConnectionADO;
			ADODB.Command adoCmd = new ADODB.Command();
			adoCmd.ActiveConnection = conn;
			string sQuery = "<DIV xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>" +sql+"</sql:query></DIV>";
			ADODB.Stream adoStreamQuery = new ADODB.Stream();
			ADODB.Stream strmOut = new ADODB.Stream();
			adoStreamQuery.Open(Missing.Value,ADODB.ConnectModeEnum.adModeWrite,ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified,"","");
			adoStreamQuery.WriteText( sQuery,ADODB.StreamWriteEnum.adWriteChar);
			adoStreamQuery.Position = 0;
			adoCmd.CommandStream = adoStreamQuery;
			adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}";
			adoCmd.Properties["Output Stream"].Value = strmOut;
			strmOut.Open(Missing.Value,ADODB.ConnectModeEnum.adModeWrite,ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified,"","");
			adoCmd.Execute(out e ,ref e ,(int)ADODB.ExecuteOptionEnum.adExecuteStream);
			conn.Close();
			switch(mode){
				case SQLXMLRequestMode.Text:
					return strmOut.ReadText(-1);
					break;
				default: return "";
			}
		}
		#endregion
	}
}
